---
sidebar_label: Tables basics
sidebar_position: 1
description: GraphQL over Postgres tables in Hasura
keywords:
  - hasura
  - docs
  - Postgres
  - schema
  - tables
---

import GraphiQLIDE from '@site/src/components/GraphiQLIDE';
import Thumbnail from '@site/src/components/Thumbnail';
import Tabs from '@theme/Tabs';
import TabItem from '@theme/TabItem';

# Postgres: Tables Basics

## Introduction

Adding tables allows you to define the GraphQL types of your schema including their corresponding fields.

## Creating tables {#pg-create-tables}

Let's say we want to create two simple tables for `articles` and `author` schema:

```sql
author (
  id SERIAL PRIMARY KEY,
  name TEXT
)

articles (
  id SERIAL PRIMARY KEY,
  title TEXT,
  content TEXT,
  rating INT,
  author_id INT
)
```

<Tabs groupId="user-preference" className="api-tabs">
<TabItem value="console" label="Console">

Open the Hasura Console and head to the `Data` tab and click on the button on the left side bar to open up an interface
to create tables.

For example, here is the schema for the `articles` table in this interface:

<Thumbnail src="/img/schema/create-table-graphql.png" alt="Schema for an article table" />

</TabItem>
<TabItem value="cli" label="CLI">

1.  [Create a migration manually](/migrations-metadata-seeds/manage-migrations.mdx#create-manual-migrations) and add the
    following SQL statement to the `up.sql` file:

    ```sql
    CREATE TABLE articles(id serial NOT NULL, title text NOT NULL, content text NOT NULL, rating integer NOT NULL, author_id serial NOT NULL, PRIMARY KEY (id));
    ```

2.  Add the following statement to the `down.sql` file in case you need to
    [roll back](/migrations-metadata-seeds/manage-migrations.mdx#roll-back-migrations)` the above statement:

    ```sql
    DROP TABLE articles;
    ```

3.  Apply the migration by running:

    ```bash
    hasura migrate apply
    ```

</TabItem>
<TabItem value="api" label="API">

You can create a table by making an API call to the [run_sql](/api-reference/schema-api/run-sql.mdx#schema-run-sql)
schema API:

```http
POST /v2/query HTTP/1.1
Content-Type: application/json
X-Hasura-Role: admin

{
  "type": "run_sql",
  "args": {
    "source": "<db_name>",
    "sql": "CREATE TABLE articles(id serial NOT NULL, title text NOT NULL, content text NOT NULL, rating integer NOT NULL, author_id serial NOT NULL, PRIMARY KEY (id));"
  }
}
```

</TabItem>
</Tabs>

## Tracking tables

Tables can be present in the underlying Postgres database without being exposed over the GraphQL API. In order to expose
a table over the GraphQL API, it needs to be **tracked**.

<Tabs groupId="user-preference" className="api-tabs">
<TabItem value="console" label="Console">

When a table is created via the Hasura Console, it gets tracked by default.

You can track any existing tables in your database from the `Data -> Schema` page:

<Thumbnail src="/img/schema/schema-track-tables.png" alt="Track table" />

</TabItem>
<TabItem value="cli" label="CLI">

1.  To track the table and expose it over the GraphQL API, edit the `tables.yaml` file in the `metadata` directory as
    follows:

    ```yaml {4-6}
    - table:
        schema: public
        name: authors
    - table:
        schema: public
        name: articles
    ```

2.  Apply the metadata by running:

    ```bash
    hasura metadata apply
    ```

</TabItem>
<TabItem value="api" label="API">

To track the table and expose it over the GraphQL API, make the following API call to the
[pg_track_table](/api-reference/metadata-api/table-view.mdx#metadata-pg-track-table) metadata API:

```http
POST /v1/metadata HTTP/1.1
Content-Type: application/json
X-Hasura-Role: admin

{
  "type": "pg_track_table",
  "args": {
    "source": "<db_name>",
    "schema": "public",
    "name": "articles"
  }
}
```

</TabItem>
</Tabs>

## Generated GraphQL schema types

As soon as a table is created and tracked, the corresponding GraphQL schema types and query/mutation fields will be
automatically generated.

The following object type is generated for the `articles` table we just created and tracked:

```graphql
# Object type
type Articles {
  id: Int
  title: String
  content: String
  rating: Int
  author_id: Int
}
```

Let's analyze the above type:

- `Articles` is the name of the type
- `id`, `title`, `content`, `rating` and `author_id` are fields of the `Articles` type
- `Int` and `String` are types that fields can have

The following query/mutation fields are generated for the `articles` table we just created and tracked:

```graphql
# Query field
articles (
  where: articles_bool_exp
  limit: Int
  offset: Int
  order_by: [articles_order_by!]
): [articles!]!

# insert/upsert mutation field
insert_articles (
  objects: [articles_insert_input!]!
  on_conflict: articles_on_conflict
): articles_mutation_response

# update mutation field
update_articles (
  where: articles_bool_exp!
  _inc: articles_inc_input
  _set: articles_set_input
): articles_mutation_response

# delete mutation field
delete_articles (
  where: articles_bool_exp!
): articles_mutation_response
```

These auto-generated fields will allow you to query and mutate data in our table.

See the [query](/api-reference/graphql-api/query.mdx) and [mutation](/api-reference/graphql-api/mutation.mdx) API
references for the full specifications.

### GraphQL types documentation

Hasura automatically picks up any comments that might have been added to your tables and columns and adds them as
GraphQL descriptions of the auto-generated types and fields.

## Try out basic GraphQL requests

At this point, you should be able to try out basic GraphQL queries/mutations on the newly created tables from the `API`
tab in the Console. _(You may want to add some sample data into the tables first)_

- Query all rows in the `articles` table:

  <GraphiQLIDE
    query={`query {
  articles {
    id
    title
    author_id
  }
}`}
    response={`{
  "data": {
    "articles": [
      {
        "id": 1,
        "title": "sit amet",
        "author_id": 4
      },
      {
        "id": 2,
        "title": "a nibh",
        "author_id": 2
      },
      {
        "id": 3,
        "title": "amet justo morbi",
        "author_id": 4
      },
      {
        "id": 4,
        "title": "vestibulum ac est",
        "author_id": 5
      }
    ]
  }
}`}
  />

- Insert data in the `author` table:

  <GraphiQLIDE
    query={`mutation add_author {
  insert_author(
    objects: [
      { name: "Jane" }
    ]
  ) {
      affected_rows
      returning {
        id
        name
      }
    }
}`}
    response={`{
  "data": {
    "insert_author": {
      "affected_rows": 1,
      "returning": [
        {
          "id": 11,
          "name": "Jane"
        }
      ]
    }
  }
}`}
  />

:::info Note

author's `id` does not need to be passed as an input as it is of type `serial` (auto incrementing integer).

:::
